/****** Object:  StoredProcedure [dbo].[AuthZ_spGetUsersByRole]    Script Date: 06/24/2009 23:47:28 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[AuthZ_spGetUsersByRole]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[AuthZ_spGetUsersByRole]
GO

/****** Object:  StoredProcedure [dbo].[AuthZ_spGetUsersByRole]    Script Date: 06/24/2009 23:47:28 ******/
SET ANSI_NULLS ON
GO

-- =============================================
-- Author:		<revans>
-- Create date: <5-17-2009>
-- Description:	<Retrieves all users for the given role (including uses in the inherited roles).>
-- Param:
-- =============================================
CREATE PROCEDURE [dbo].[AuthZ_spGetUsersByRole]

	@role as nvarchar(50) = ''

AS
BEGIN
	BEGIN TRY
		SET NOCOUNT ON;
		
		DECLARE @roles AS Table (RoleName nvarchar(50))
		
		--Load table variable with all roles in the hierarchy
		--including the given role.
		INSERT INTO @roles SELECT @role
		INSERT INTO @roles EXEC authz_spGetParentRoles @role
		
		--Get all users from all the roles.
		SELECT DISTINCT userRole.PersonID
		, userRole.FullName
		, userRole.EMailName
		, userRole.DisplayName
		, userRole.PersonnelNumber
		, userRole.Alias
		FROM authZ_vwUserRole userRole
		JOIN @roles roles
		ON   roles.RoleName = userRole.RoleName

	END TRY
BEGIN CATCH
	DECLARE @ErrorMessage NVARCHAR(4000);
    DECLARE @ErrorSeverity INT;
    DECLARE @ErrorState INT;

    SELECT @ErrorMessage = ERROR_MESSAGE(),
           @ErrorSeverity = ERROR_SEVERITY(),
           @ErrorState = ERROR_STATE();
           
    RAISERROR (@ErrorMessage, -- Message text.
               @ErrorSeverity, -- Severity.
               @ErrorState -- State.
               );
END CATCH
END

GO


